package net.dgg.rz.common.utils.excel;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>@Title EXCEL工具类</p>
 * <p>@author HDC</p>
 * <p>@date 2018/5/7 10:46 </p>
 * <p>@Copyright © dgg group.All Rights Reserved. 版权信息</p>
 */

public class ExcelUtils<E> {

    private E e;
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private int etimes = 0;

    public ExcelUtils(E e) {
        this.e = e;
    }

    @SuppressWarnings("unchecked")
    public E get() throws InstantiationException, IllegalAccessException {
        return (E) e.getClass().newInstance();
    }

    /**
     * 将数据写入到Excel文件
     *
     * @param filePath
     *            文件路径
     * @param sheetName
     *            工作表名称
     * @param title
     *            工作表标题栏
     * @param data
     *            工作表数据
     * @throws FileNotFoundException
     *             文件不存在异常
     * @throws IOException
     *             IO异常
     */
    public static void writeToFile(String filePath, String[] sheetName, List<? extends Object[]> title, List<? extends List<? extends Object[]>> data) throws FileNotFoundException, IOException {
        // 创建并获取工作簿对象
        Workbook wb = getWorkBook(sheetName, title, data);
        // 写入到文件
        FileOutputStream out = new FileOutputStream(filePath);
        wb.write(out);
        out.close();
    }

    /**
     * 创建工作簿对象<br>
     * <font color="red">工作表名称，工作表标题，工作表数据最好能够对应起来</font><br>
     * 比如三个不同或相同的工作表名称，三组不同或相同的工作表标题，三组不同或相同的工作表数据<br>
     * <b> 注意：<br>
     * 需要为每个工作表指定<font color="red">工作表名称，工作表标题，工作表数据</font><br>
     * 如果工作表的数目大于工作表数据的集合，那么首先会根据顺序一一创建对应的工作表名称和数据集合，然后创建的工作表里面是没有数据的<br>
     * 如果工作表的数目小于工作表数据的集合，那么多余的数据将不会写入工作表中 </b>
     *
     * @param sheetName
     *            工作表名称的数组
     * @param title
     *            每个工作表名称的数组集合
     * @param data
     *            每个工作表数据的集合的集合
     * @return Workbook工作簿
     * @throws FileNotFoundException
     *             文件不存在异常
     * @throws IOException
     *             IO异常
     */
    public static Workbook getWorkBook(String[] sheetName, List<? extends Object[]> title, List<? extends List<? extends Object[]>> data) throws FileNotFoundException, IOException {

        // 创建工作簿
        Workbook wb = new SXSSFWorkbook();
        // 创建一个工作表sheet
        Sheet sheet = null;
        // 申明行
        Row row = null;
        // 申明单元格
        Cell cell = null;
        // 单元格样式
        CellStyle titleStyle = wb.createCellStyle();
        CellStyle cellStyle = wb.createCellStyle();
        // 字体样式
        Font font = wb.createFont();
        // 粗体
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        titleStyle.setFont(font);
        // 水平居中
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 垂直居中
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        // 水平居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        cellStyle.setFillBackgroundColor(HSSFColor.BLUE.index);

        // 标题数据
        Object[] title_temp = null;

        // 行数据
        Object[] rowData = null;

        // 工作表数据
        List<? extends Object[]> sheetData = null;

        // 遍历sheet
        for (int sheetNumber = 0; sheetNumber < sheetName.length; sheetNumber++) {
            // 创建工作表
            sheet = wb.createSheet();
            // 设置默认列宽
            sheet.setDefaultColumnWidth(18);
            // 设置工作表名称
            wb.setSheetName(sheetNumber, sheetName[sheetNumber]);
            // 设置标题
            title_temp = title.get(sheetNumber);
            row = sheet.createRow(0);

            // 写入标题
            for (int i = 0; i < title_temp.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(title_temp[i].toString());
            }

            try {
                sheetData = data.get(sheetNumber);
            } catch (Exception e) {
                continue;
            }
            // 写入行数据
            for (int rowNumber = 0; rowNumber < sheetData.size(); rowNumber++) {
                // 如果没有标题栏，起始行就是0，如果有标题栏，行号就应该为1
                row = sheet.createRow(title_temp == null ? rowNumber : (rowNumber + 1));
                rowData = sheetData.get(rowNumber);
                for (int columnNumber = 0; columnNumber < rowData.length; columnNumber++) {
                    cell = row.createCell(columnNumber);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(rowData[columnNumber] + "");
                }
            }
        }
        return wb;
    }

    /**
     * 将数据写入到EXCEL文档
     *
     * @param list
     *            数据集合
     * @param edf
     *            数据格式化，比如有些数字代表的状态，像是0:女，1：男，或者0：正常，1：锁定，变成可读的文字
     *            该字段仅仅针对Boolean,Integer两种类型作处理
     * @param fileName
     *            文件名称
     *  @param vo
     *           导出实体对象
     * @throws Exception
     */
    public static <T> void writeToFile(List<T> list, ExcelDataFormatter edf, String fileName,HttpServletResponse httpServletResponse,Class vo) throws Exception {
        // 创建并获取工作簿对象
        Workbook wb = getWorkBook(list, edf,vo);
        OutputStream servletOutputStream = new BufferedOutputStream(httpServletResponse.getOutputStream());
        httpServletResponse.setContentType("application/octet-stream; charset=UTF-8");
        httpServletResponse.setHeader("Content-Disposition", "attachment; filename=\"" + new String(fileName.getBytes("GBK"), "ISO8859-1") + "\"");
        // 写入到文件
        wb.write(servletOutputStream);
        servletOutputStream.close();
    }

    /**
     * 将数据写入到EXCEL文档
     *
     * @param wb 生成的文档对象
     * @param fileName
     *            文件名称
     * @throws Exception
     */
    public static <T> void writeToFile(Workbook wb, String fileName,HttpServletResponse response) {
        if(wb==null){
            throw new RuntimeException("Workbook不可为空");
        }
        // 指定下载的文件名
        response.setHeader("Content-Disposition",  "attachment;filename*=UTF-8''"+ fileName);
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        //查询出来的数据
        OutputStream output;
        try {
            output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获得Workbook对象
     *
     * @param list
     *            数据集合
     * @return Workbook
     * @throws Exception
     */
    public static <T> Workbook getWorkBook(List<T> list, ExcelDataFormatter edf,Class vo) throws Exception {
        // 创建工作簿
        Workbook wb = new SXSSFWorkbook();

        // 创建一个工作表sheet
        Sheet sheet = wb.createSheet();
        // 申明行
        Row row = sheet.createRow(0);
        // 申明单元格
        Cell cell = null;

        CreationHelper createHelper = wb.getCreationHelper();

        Field[] fields = ReflectUtils.getClassFieldsAndSuperClassFields(vo);

        XSSFCellStyle titleStyle = (XSSFCellStyle) wb.createCellStyle();
//        titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        // 设置前景色
//        titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(159, 213, 183)));
//        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);

        Font font = wb.createFont();
//        font.setColor(HSSFColor.BROWN.index);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 设置字体
        titleStyle.setFont(font);

        int columnIndex = 0;
        Excel excel = null;
        for (Field field : fields) {
            field.setAccessible(true);
            excel = field.getAnnotation(Excel.class);
            if (excel == null || excel.skip() == true) {
                continue;
            }
            // 列宽注意乘256
            sheet.setColumnWidth(columnIndex, excel.width() * 256);
            // 写入标题
            cell = row.createCell(columnIndex);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(excel.name());

            columnIndex++;
        }

        int rowIndex = 1;

        CellStyle cs = wb.createCellStyle();

        if (list == null || list.size() == 0) {
            return wb;
        }

        for (T t : list) {
            row = sheet.createRow(rowIndex);
            columnIndex = 0;
            Object o = null;
            for (Field field : fields) {

                field.setAccessible(true);

                // 忽略标记skip的字段
                excel = field.getAnnotation(Excel.class);
                if (excel == null || excel.skip() == true) {
                    continue;
                }
                // 数据
                cell = row.createCell(columnIndex);

                o = field.get(t);
                // 如果数据为空，跳过
                if (o == null) {
                	o = "";
                }
                   
                // 处理日期类型
                if (o instanceof Date) {
                    cs.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
                    cell.setCellStyle(cs);
                    cell.setCellValue((Date) field.get(t));
                } else if (o instanceof Double || o instanceof Float) {
                    cell.setCellValue((Double) field.get(t));
                } else if (o instanceof Boolean) {
                    Boolean bool = (Boolean) field.get(t);
                    if (edf == null) {
                        cell.setCellValue(bool);
                    } else {
                        Map<String, String> map = edf.get(field.getName());
                        if (map == null) {
                            cell.setCellValue(bool);
                        } else {
                        	String vl = map.get(bool.toString().toLowerCase());
                        	if(vl == null) {
                        		cell.setCellValue(bool);
                        	}else {
                        		cell.setCellValue(vl);
                        	}
                        }
                    }

                } else if (o instanceof Integer) {

                    Integer intValue = (Integer) field.get(t);

                    if (edf == null) {
                        cell.setCellValue(intValue);
                    } else {
                        Map<String, String> map = edf.get(field.getName());
                        if (map == null) {
                            cell.setCellValue(intValue);
                        } else {
                        	String vl = map.get(intValue.toString());
                        	if(vl == null) {
                        		cell.setCellValue(intValue);
                        	}else {
                        		cell.setCellValue(vl);
                        	}
                        }
                    }
                }else if (o instanceof String) {

                    String string = (String) field.get(t);

                    if (edf == null) {
                        cell.setCellValue(string);
                    } else {
                        Map<String, String> map = edf.get(field.getName());
                        if (map == null) {
                            cell.setCellValue(string);
                        } else {
                        	String vl = map.get(string);
                        	if(vl == null) {
                        		cell.setCellValue(string);
                        	}else {
                        		cell.setCellValue(vl);
                        	}
                        }
                    }
                } else {
                    cell.setCellValue(field.get(t).toString());
                }

                columnIndex++;
            }

            rowIndex++;
        }

        return wb;
    }

    /**
     * 从文件读取数据，最好是所有的单元格都是文本格式，日期格式要求yyyy-MM-dd HH:mm:ss,布尔类型0：真，1：假
     *
     * @param edf
     *            数据格式化
     *
     * @param is
     *            输入流
     * @return
     * @throws Exception
     */
    public List<E> readFromFile(ExcelDataFormatter edf, InputStream is) throws Exception {
        Field[] fields = ReflectUtils.getClassFieldsAndSuperClassFields(e.getClass());

        Map<String, String> textToKey = new HashMap<String, String>();

        Excel _excel = null;
        for (Field field : fields) {
            _excel = field.getAnnotation(Excel.class);
            if (_excel == null || _excel.skip() == true) {
                continue;
            }
            textToKey.put(_excel.name(), field.getName());
        }

        //Workbook wb = new XSSFWorkbook(is);
        Workbook wb = WorkbookFactory.create(is); 

        Sheet sheet = wb.getSheetAt(0);
        Row title = sheet.getRow(0);
        // 标题数组，后面用到，根据索引去标题名称，通过标题名称去字段名称用到 textToKey
        String[] titles = new String[title.getPhysicalNumberOfCells()];
        for (int i = 0; i < title.getPhysicalNumberOfCells(); i++) {
            titles[i] = title.getCell(i).getStringCellValue();
        }

        List<E> list = new ArrayList<E>();

        E e = null;

        int rowIndex = 0;
        int columnCount = titles.length;
        Cell cell = null;
        Row row = null;

        for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) {

            row = it.next();
            if (rowIndex++ == 0) {
                continue;
            }

            if (row == null) {
                break;
            }

            e = get();

            for (int i = 0; i < columnCount; i++) {
                cell = row.getCell(i);
                etimes = 0;
                readCellContent(textToKey.get(titles[i]), fields, cell, e, edf);
            }
            list.add(e);
        }
        return list;
    }

    /**
     * 从单元格读取数据，根据不同的数据类型，使用不同的方式读取<br>
     * 有时候POI自作聪明，经常和我们期待的数据格式不一样，会报异常，<br>
     * 我们这里采取强硬的方式<br>
     * 使用各种方法，知道尝试到读到数据为止，然后根据Bean的数据类型，进行相应的转换<br>
     * 如果尝试完了（总共7次），还是不能得到数据，那么抛个异常出来，没办法了
     *
     * @param key
     *            当前单元格对应的Bean字段
     * @param fields
     *            Bean所有的字段数组
     * @param cell
     *            单元格对象
     * @param e
     * @throws Exception
     */
    public void readCellContent(String key, Field[] fields, Cell cell, E e, ExcelDataFormatter edf) throws Exception {

        Object o = null;
        try {
        	if(cell != null) {
        		switch (cell.getCellType()) {
	                case XSSFCell.CELL_TYPE_BOOLEAN:
	                    o = cell.getBooleanCellValue();
	                    break;
	                case XSSFCell.CELL_TYPE_NUMERIC:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
	                    //o = cell.getNumericCellValue();
                        o = cell.getStringCellValue();
	                    /*if (HSSFDateUtil.isCellDateFormatted(cell)) {
	                        o = DateUtil.getJavaDate(cell.getNumericCellValue());
	                    }*/
	                    break;
	                case XSSFCell.CELL_TYPE_STRING:
	                    o = cell.getStringCellValue();
	                    break;
	                case XSSFCell.CELL_TYPE_ERROR:
	                    o = cell.getErrorCellValue();
	                    break;
	                case XSSFCell.CELL_TYPE_BLANK:
	                    o = null;
	                    break;
	                case XSSFCell.CELL_TYPE_FORMULA:
	                    o = cell.getCellFormula();
	                    break;
	                default:
	                    o = null;
	                    break;
        		}
        	}
            

            if (o == null) {
                return;
            }

            for (Field field : fields) {
                field.setAccessible(true);
                if (field.getName().equals(key)) {
                    Boolean bool = true;
                    Map<String, String> map = null;
                    if (edf == null) {
                        bool = false;
                    } else {
                        map = edf.get(field.getName());
                        if (map == null) {
                            bool = false;
                        }
                    }

                    if (field.getType().equals(Date.class)) {
                        if (o.getClass().equals(Date.class)) {
                            field.set(e, o);
                        } else {
                            field.set(e, sdf.parse(o.toString()));
                        }
                    } else if (field.getType().equals(String.class)) {
                        if (o.getClass().equals(String.class)) {
                            if(null != edf){
                                field.set(e, edf.get(field.getName()).get(o));
                            }else{
                                field.set(e, o.toString());
                            }
                        } else {
                            field.set(e, o.toString());
                        }
                    } else if (field.getType().equals(Long.class)) {
                        if (o.getClass().equals(Long.class)) {
                            field.set(e, o);
                        } else {
                            field.set(e, Long.parseLong(o.toString()));
                        }
                    } else if (field.getType().equals(Integer.class)) {
                        if (o.getClass().equals(Integer.class)) {
                            field.set(e, o);
                        } else {
                            // 检查是否需要转换
                            if (bool) {
                                field.set(e, map.get(o.toString()) != null ? Integer.parseInt(map.get(o.toString())) : Integer.parseInt(o.toString()));
                            } else {
                                field.set(e, Integer.parseInt(o.toString()));
                            }
                        }
                    } else if (field.getType().equals(BigDecimal.class)) {
                        if (o.getClass().equals(BigDecimal.class)) {
                            field.set(e, o);
                        } else {
                            field.set(e, BigDecimal.valueOf(Double.parseDouble(o.toString())));
                        }
                    } else if (field.getType().equals(Boolean.class)) {
                        if (o.getClass().equals(Boolean.class)) {
                            field.set(e, o);
                        } else {
                            // 检查是否需要转换
                            if (bool) {
                                field.set(e, map.get(o.toString()) != null ? Boolean.parseBoolean(map.get(o.toString())) : Boolean.parseBoolean(o.toString()));
                            } else {
                                field.set(e, Boolean.parseBoolean(o.toString()));
                            }
                        }
                    } else if (field.getType().equals(Float.class)) {
                        if (o.getClass().equals(Float.class)) {
                            field.set(e, o);
                        } else {
                            field.set(e, Float.parseFloat(o.toString()));
                        }
                    } else if (field.getType().equals(Double.class)) {
                        if (o.getClass().equals(Double.class)) {
                            field.set(e, o);
                        } else {
                            field.set(e, Double.parseDouble(o.toString()));
                        }

                    }

                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            // 如果还是读到的数据格式还是不对，只能放弃了
            if (etimes > 7) {
                throw ex;
            }
            etimes++;
            if (o == null) {
                readCellContent(key, fields, cell, e, edf);
            }
        }
    }

    /**
     * @Title: changeToString
     * @Description: 将Cell对象转换成String类型（导入时调用）
     * @param cell
     * @return
     * @return String    返回类型
     */
    public static String cellValue(Cell cell)
    {
        String strCell = "";
        if (cell != null)
        {
            switch (cell.getCellType())
            {
                case Cell.CELL_TYPE_FORMULA:
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    strCell = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell))
                    {
                        Date d = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                        //DateFormat format= new SimpleDateForma;
                        strCell = formater.format(d);
                        //只有日期的转化
                        strCell = strCell.contains("00:00")?strCell.substring(0,10):strCell;
                    }
                    else
                    {
                        strCell = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    strCell = cell.getStringCellValue();
                    break;
                default:
            }
        }
        return strCell.trim();
    }

    /**
     * 导出excel合并列
     *
     * @param clazz
     * @param entityList 需要导出的数据
     * @param chopper        根据pId字段进行合并列   如： wordSort
     */
    public static <T> HSSFWorkbook getMergeExcel( Class<T> clazz, List<T> entityList,String sheetName, String chopper) {

        Map<String, List<T>> tempMap = new HashMap<>();//临时过渡的 map

        List<List<T>> lists = new ArrayList<>();//每一个值对应一个合并形成的行
        for (int i = 0; i < entityList.size(); i++) {
            try {
                T t = entityList.get(i);
                Field declaredField = t.getClass().getDeclaredField(chopper);
                declaredField.setAccessible(true);
                Object fieldValue = declaredField.get(t);
                if (fieldValue == null){
                    throw new NullPointerException("Field " + chopper + "  null ");
                }
                List<T> listEntity = tempMap.get(fieldValue.toString());//
                if (listEntity == null) {//没有相同的key说明需要开启新的合并行
                    tempMap.put(fieldValue.toString(), listEntity = new ArrayList<>());//把相同 pId 字段值的的放在一起  后面好合并
                    lists.add(listEntity);//
                }
                listEntity.add(t);//把 chopper 这个字段相同的值的放在一起
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
        }

        tempMap.clear();//用完了
        System.gc();

        //第一步创建workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle style = wb.createCellStyle();
//        style.setVerticalAlignment(VerticalAlignment.CENTER);
//        style.setAlignment(HorizontalAlignment.CENTER);

        //第二步创建sheet
        HSSFSheet sheet = wb.createSheet(sheetName == null || "".equals(sheetName) ? "sheetName" : sheetName);
        HSSFRow row0 = sheet.createRow(0);

        List<Integer> mergeList = new ArrayList<>();//需要合并的列

        Map<Integer, Method> methodMap = new HashMap<>();
        Method[] declaredMethods = clazz.getDeclaredMethods();
        for (int i = 0; i < declaredMethods.length; i++) {
            Method method = declaredMethods[i];
            if (method.isAnnotationPresent(Excel.class)) {// 取出含有 CellValue 注解的方法  后面要用 column 这个值
                Excel annotation = method.getAnnotation(Excel.class);
                sheet.autoSizeColumn(annotation.column());
                methodMap.put(annotation.column(), method);
                if ("".equals(annotation.name())) {
                    System.err.println(method.getName() + "\tcolumn:" + annotation.column() + "\ttitle:" + annotation.name());
                }
                HSSFCell cell = row0.createCell(annotation.column());
                sheet.setColumnWidth(annotation.column(), annotation.mergeWidth());
                cell.setCellStyle(style);
                cell.setCellValue(annotation.name());
                if (annotation.merge()) {//判断是否需要合并
                    mergeList.add(annotation.column());// 把需要合并的列加入  集合， 后面要用
                }
            }
        }
        int rownum = row0.getRowNum() + 1;//第几行
        int firstRow = 1;//起始行号

        for (List<T> list : lists) {//每一个list对应一个合并行
            int lastRow = firstRow + list.size() - 1;//终止行号
            for (int i = 0; i < list.size(); i++) {
                HSSFRow row = sheet.createRow(rownum);//创建新的一行
                Object entity = list.get(i);
                for (Integer column : methodMap.keySet()) {//下面是给新创建的一行的每一列赋值
                    try {
                        Method method = methodMap.get(column);
                        method.setAccessible(true);
                        Object invoke = method.invoke(entity);
                        if (invoke == null) {//没有返回值
                            continue;
                        }
                        HSSFCell cell = row.createCell(column);
                        cell.setCellStyle(style);
                        if (invoke instanceof String) {//判断类型   还有一些没有判断  直接就当String 类型处理了
                            cell.setCellValue((String) invoke);
                        } else if (invoke instanceof Integer) {
                            cell.setCellValue((Integer) invoke);
                        } else if (invoke instanceof Date) {
                            cell.setCellValue((Date) invoke);
                        } else {
                            cell.setCellValue(invoke != null ? invoke.toString() : "");
                        }
                    } catch (IllegalAccessException e1) {
                        e1.printStackTrace();
                    } catch (InvocationTargetException e1) {
                        e1.printStackTrace();
                    }
                }
                ++rownum;//加一行
            }
            if (list.size() > 1){//如果只有一条数据就不需要合并，  要不然要报异常
                for (Integer mergeColumn : mergeList) {
                    // 起始行号，终止行号， 起始列号，终止列号
                    sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, mergeColumn, mergeColumn));
                }
            }
            firstRow += list.size();//得到下一次要开始合并的开始行
        }
        return wb;
    }

    /**
     * 导出excel复杂表头
     *
     * @param clazz
     * @param entityList 需要导出的数据
     * @param tableName 生成表名
     * @param pid 根据pId指定的字段进行合并列(如果没有唯一字段请在实体中自己构造)
     */
    public static <T> HSSFWorkbook getMergeHeadAndDataExcel(HSSFWorkbook wb,Class<T> clazz, List<T> entityList, String tableName,String pid) {
        Map<String, List<T>> map = new LinkedHashMap<>();
        for (int i = 0; i <entityList.size(); i++) {
            try {
                T t = entityList.get(i);
                Field declaredField = t.getClass().getDeclaredField(pid);
                declaredField.setAccessible(true);
                Object fieldValue = declaredField.get(t);
                List<T> listEntity = map.get(fieldValue.toString());
                if (listEntity == null) {
                    map.put(fieldValue.toString(), listEntity = new ArrayList<>());//把相同 pId 字段值的的放在一起  后面好合并
                }
                listEntity.add(t);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
        }

        List<Integer> mergeList = new ArrayList<>();//需要合并的列
        Map<String, Method> methodMap = new HashMap<>();
        Method[] declaredMethods = clazz.getDeclaredMethods();
        for (int i = 0; i < declaredMethods.length; i++) {
            Method method = declaredMethods[i];
            if (method.isAnnotationPresent(Excel.class)) {// 取出含有 Excel 注解的方法  后面要用 column 这个值
                methodMap.put(method.getName(), method);
                Excel annotation = method.getAnnotation(Excel.class);
                if ("".equals(annotation.name())) {
                    System.err.println(method.getName() + "\tname:" + annotation.column() + "\tname:" + annotation.name());
                }
                if (annotation.merge()) {//判断是否需要合并
                    mergeList.add(annotation.column());// 把需要合并的列加入  集合， 后面要用
                }
            }
        }

        HSSFSheet sheet = wb.getSheet(tableName);
        int rownum = sheet.getLastRowNum() + 1;//第几行
        int firstRow = 1;//起始行号
        BigDecimal bigDecimal;
        for (String key : map.keySet()) {

            List<T> list = map.get(key);
            int lastRow = firstRow + list.size() - 1;//终止行号
            for (int i = 0; i < list.size(); i++) {
                HSSFRow row = sheet.createRow(rownum);//创建新的一行
                Object entity = list.get(i);
                for (String methodName : methodMap.keySet()) {//下面是给新创建的一行的每一列赋值
                    try {
                        Method method = methodMap.get(methodName);
                        Excel annotation = method.getAnnotation(Excel.class);
                        method.setAccessible(true);
                        Object invoke = method.invoke(entity);
                        if (invoke == null) {//没有返回值
                            continue;
                        }
                        if(invoke instanceof BigDecimal || invoke instanceof Double){
                            bigDecimal = new BigDecimal(String.valueOf(invoke));
                            invoke = bigDecimal.toPlainString();
                        }
                        int column = annotation.column();
                        row.createCell(column,HSSFCell.CELL_TYPE_STRING).setCellValue(invoke != null ? invoke.toString() : "");
                    } catch (IllegalAccessException e1) {
                        e1.printStackTrace();
                    } catch (InvocationTargetException e1) {
                        e1.printStackTrace();
                    }
                }
                ++rownum;//加一行
            }
            for (Integer mergeColumn : mergeList) {
                // 起始行号，终止行号， 起始列号，终止列号
                sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, mergeColumn, mergeColumn));
            }
            firstRow += list.size();//得到下一次要开始合并的开始行
        }
        return wb;
    }
}
